DECLARE @ProductName varchar(128)
DECLARE @Major int
DECLARE @Minor int
DECLARE @EnvCode varchar(50)

SET @ProductName = 'eCare_ProductionSupport'
SET @Major = 5
SET @Minor = 0
SET @EnvCode = 'ITEST01'

DECLARE @ProductID int
DECLARE @OnTimePath varchar(1000)

SELECT @ProductID = OID
     , @OnTimePath = OnTimeProjectPath
  FROM Products
 WHERE Name = @ProductName

-- #############################################################################
-- First Pass 
--            - Insert into memory Table @DeploymentBuilds1
--              the Deployments for this product to the Environment
--              for the specified Major and Miner
-- #############################################################################

DECLARE @DeploymentBuilds1 TABLE 
( DeploymentTime datetime
, Version varchar(20)
, BuildID int
)

INSERT INTO @DeploymentBuilds1 ( DeploymentTime, Version, BuildID)
SELECT MAX(de.CreatedOn) DeploymentTime
     , MAX(b.Version) Version
     , b.OID BuildID
  FROM DeploymentEvent de
       LEFT JOIN Builds b
       ON de.BuildID = b.OID
       LEFT JOIN ProductEnvironments pe
       ON de.ProductEnvironmentID = pe.oid
       LEFT JOIN TypesOfEnvironment toe
       ON pe.EnvironmentID = toe.OID
 WHERE b.ProductID = @ProductID
   AND b.Major = @Major
   AND b.Minor = @Minor
   AND toe.EnvironmentCode = @EnvCode
group by b.OID

-- #############################################################################
-- Second Pass 
--            - Insert into memory Table @DeploymentBuilds
--              the Deployments from @DeploymentBuilds1
--              but add Previous Build number (which is -1 for first)
-- #############################################################################

DECLARE @DeploymentBuilds TABLE 
( DeploymentTime datetime
, Version varchar(20)
, BuildID int
, PrevBuildID int
)


INSERT INTO @DeploymentBuilds ( DeploymentTime, Version, BuildID, PrevBuildID)
SELECT *
     , (SELECT  ISNULL(MAX(db2.BuildID) ,-1)
          FROM @DeploymentBuilds1 db2
         WHERE db2.BuildID < db1.BuildID) PrevBuildID
  FROM @DeploymentBuilds1 db1

-- #############################################################################
-- Build #BuildDeploymentMap
--            - This table contains the DeploymentTime and Version
--            - for when a Build Version got deployed to this Environment
-- #############################################################################
IF OBJECT_ID('tempdb..#BuildDeploymentMap') is not null
DROP TABLE #BuildDeploymentMap

CREATE TABLE #BuildDeploymentMap 
( BuildVersion varchar(20)
, DeploymentVersion varchar(20)
, DeploymentTime datetime
)

INSERT INTO #BuildDeploymentMap (BuildVersion, DeploymentVersion, DeploymentTime)
SELECT b.Version BuildVersion
     , db.Version DeploymentVersion
     , db.DeploymentTime
  FROM @DeploymentBuilds db
     , Builds b
 WHERE b.OID > db.PrevBuildID
   AND b.OID <= db.BuildID
   AND b.Major = @Major
   AND b.Minor = @Minor
   AND b.ProductID = @ProductID
   AND b.OID <= db.BuildID

-- SELECT * FROM #BuildDeploymentMap

-- #############################################################################

DECLARE @OnTimeProjectID int

SELECT @OnTimeProjectID=ProjectID
  FROM OnTime.dbo.fnProjectPathTree()
 WHERE path = @OnTimePath

IF OBJECT_ID('tempdb..#DeploymentReport') is not null
DROP TABLE #DeploymentReport

CREATE TABLE #DeploymentReport 
     ( DeploymentVersion varchar(20)
     , DeploymentTime datetime
     , DefectId int
     , Name nvarchar(150)
     , Priority nvarchar(25)
     , Severity nvarchar(25)
     , Description varchar(max)
     , Notes varchar(max)
     , ReplicationProcedures varchar(max)
     , DefectStatus varchar(25)
     , BuildVersion varchar(20)
     )

INSERT INTO #DeploymentReport 
     ( DeploymentVersion
     , DeploymentTime
     , DefectId 
     , Name
     , Priority
     , Severity
     , Description 
     , Notes
     , ReplicationProcedures
     , DefectStatus 
     , BuildVersion
     )
SELECT bdm.DeploymentVersion
     , bdm.DeploymentTime
     , d.DefectId
     , d.Name
     , pt.Name Priority
     , sevt.Name Severity
     , d.Description
     , d.Notes
     , d.ReplicationProcedures
     , st.Name DefectStatus
     , bdm.BuildVersion
  FROM OnTime.dbo.Defects d
       LEFT JOIN OnTime.dbo.StatusTypes st
       ON d.StatusTypeId = st.StatusTypeId
       LEFT JOIN OnTime.dbo.PriorityTypes pt
       ON d.PriorityTypeId = pt.PriorityTypeId
       LEFT JOIN OnTime.dbo.SeverityTypes sevt
       ON d.SeverityTypeId = sevt.SeverityTypeId
     , #BuildDeploymentMap bdm
 WHERE d.ProjectID IN
       ( SELECT ProjectID
           FROM OnTime.dbo.fnProjectTreeByID(@OnTimeProjectID)
       )
   AND d.BuildNumberOfFix = bdm.BuildVersion
   --AND st.Name NOT IN ('Closed')
ORDER BY DeploymentVersion desc


SELECT 1 AS Tag
     , NULL AS Parent
     , DeploymentVersion AS [Deployment!1!DeploymentVersion]
     , DeploymentTime AS [Deployment!1!DeploymentTime]
     , NULL AS [Defect!2!BuildVersion]
     , NULL AS [Defect!2!DefectStatus]
     , NULL AS [Defect!2!DefectId]
     , NULL AS [Defect!2!Name]
     , NULL AS [Defect!2!Priority]
     , NULL AS [Defect!2!Severity]
     , NULL AS [Defect!2!Description!element]
     , NULL AS [Defect!2!Notes!element]
     , NULL AS [Defect!2!ReplicationProcedures!element]
  FROM #DeploymentReport dr

UNION

SELECT 2 AS Tag
     , 1 AS Parent
     , DeploymentVersion
     , DeploymentTime
     , BuildVersion
     , DefectStatus
     , DefectId
     , Name
     , Priority
     , Severity
     , Description
     , Notes
     , ReplicationProcedures
  FROM #DeploymentReport dr

 ORDER BY 3 desc, 7
  FOR XML EXPLICIT, root('Deployments')
